# %%
# 初始化
import pandas as pd

import reader
from common import *

(year, month), (year_new, month_new) = year_month()
year_month_list = last_n_month(year, month)

FP_GONGDAN_DANGYUE = (
    f"data/0-工单/家客工单导出_潮州_{year}-{month:02}.zip"
)
FP_GUANXIAN = f"data/0-管线/{year}{month:02}_管线数据.zip"
FP_HUGAN = f"data/0-互感/{year}{month:02}_互感数据.zip"
FP_JIHE_MINGXI = (
    f"data/4-IMS装移机/{year}{month:02}_IMS工单地市稽核明细报表.xlsx"
)
FP_TONGHUA = f"data/4-IMS装移机/{year}{month:02}_BI-IMS月报.xlsx"
FP_ZHIJIAN = f"data/2-宽带装移机/{year}{month:02}_智能质检清单.xlsx"
FP_KUANDAI_JIHE_PKL = (
    f"pickle/{year}{month:02}_宽带装移机工单地市稽核明细表.pkl"
)


check_files(
    FP_GONGDAN_DANGYUE,
    FP_GUANXIAN,
    FP_HUGAN,
    FP_JIHE_MINGXI,
    FP_TONGHUA,
    FP_ZHIJIAN,
    FP_KUANDAI_JIHE_PKL,
)

# %%
# 读取工单
usecols = (
    "工单号",
    "宽带帐号",
    "多媒体家庭电话",
    "CRM工单号",
    "CRM业务流水号",
    "操作类型",
    "接入方式",
    "是否施工标识",
    "产品名称",
    "派单日期",
    "结单时间",
    "工单状态",
    "资源类型",
    "标准地址",
    "产品业务属性",
    "首次第三方复核结果",
)
df = reader.read_gongdan(FP_GONGDAN_DANGYUE, usecols)

# %%
# 工单筛选
# 操作类型 in 业务开通、业务移机
# 是否施工 in 普通开通施工、NaN
# 产品名称 = 手机多媒体家庭电话属性产品
# 工单状态 = 归档
mask = (
    (
        ((df.操作类型 == "业务开通") & (df.产品业务属性 == "新增业务"))
        | (df.操作类型 == "业务移机")
    )
    & ((df["是否施工标识"] == "普通开通施工") | df["是否施工标识"].isna())
    & (df["产品名称"] == "手机多媒体家庭电话属性产品")
    & (df["工单状态"] == "归档")
)
df = df[mask]

# %%
# 读取系统：IMS工单地市稽核明细报表
columns = [
    "工单号",
    "CRM单号",
    "宽带号码",
    "固话号码",
    "操作类型",
    "派单时间",
    "归档日期",
    "接入模式",
    "90天同址同帐号先拆后装核查",
    "当月同帐号先装后拆核查",
    "90天内同帐号先装后移核查",
    "90内同帐号多次移机核查",
    "当月相同联系电话装机工单核查",
    "当月新增一址多户工单核查",
    "登录端口异常工单核查",
    "当月登录端口与资源系统不一致核查",
    "服开工单规范性质检",
    "弱光核查",
    "系统稽核结果",
]
df_sgs = pd.read_excel(FP_JIHE_MINGXI, usecols=columns)

# 90天同址同帐号先拆后装核查:取系统稽核结果
# 当月同帐号先装后拆核查:取系统稽核结果
# 90天内同帐号先装后移核查:取系统稽核结果
# 90内同帐号多次移机核查:取系统稽核结果
copy_columns = [
    "90天同址同帐号先拆后装核查",
    "当月同帐号先装后拆核查",
    "90天内同帐号先装后移核查",
    "90内同帐号多次移机核查",
]

df = df.merge(
    df_sgs[["工单号"] + copy_columns],
    on="工单号",
    how="left",
)

# %%
# 当月相同联系电话装机工单核查:不稽核
df["当月相同联系电话装机工单核查"] = S_BYPASSED

# %%
# 当月新增一址多户工单核查
df_guanxian = reader.read_csv(
    FP_GUANXIAN,
    columns=["宽带号码", "七级地址ID"],
)
guanxian_err_addr = df_guanxian.groupby("七级地址ID").filter(
    lambda x: len(x) >= 5
)["宽带号码"]
df["当月新增一址多户工单核查"] = S_PASSED
df.loc[
    df["宽带帐号"].isin(guanxian_err_addr), "当月新增一址多户工单核查"
] = S_NOT_PASSED


# %%
# 登录端口异常工单核查
df["登录端口异常工单核查"] = S_PASSED
df_hugan = reader.read_csv(
    FP_HUGAN,
    columns=(
        "宽带号码",
        "ONU下联端口",
        "所属ONU设备名称",
        "错误类型",
    ),
)
df_hugan_err_port = df_hugan.groupby(
    ["ONU下联端口", "所属ONU设备名称"]
).filter(lambda x: len(x) >= 2)["宽带号码"]
df.loc[
    df["宽带帐号"].isin(df_hugan_err_port), "登录端口异常工单核查"
] = S_NOT_PASSED

# %%
# 当月登录端口与资源系统不一致核查
df["当月登录端口与资源系统不一致核查"] = S_NOT_PASSED
df_hugan_ok_ziyuan = df_hugan[df_hugan["错误类型"] == "正确资源"][
    "宽带号码"
]
df.loc[
    df["宽带帐号"].isin(df_hugan_ok_ziyuan), "当月登录端口与资源系统不一致核查"
] = S_PASSED

# %%
# 服开工单规范性质检
df_znzj = pd.read_excel(
    FP_ZHIJIAN,
    usecols=("工单编号", "业务类型"),
    dtype="string",
    skiprows=1,
)
df_znzj = df_znzj[df_znzj["业务类型"] == "IMS"]["工单编号"]
mask = df["工单号"].isin(df_znzj) | (
    df["首次第三方复核结果"] == S_NOT_PASSED
)
df["服开工单规范性质检"] = S_PASSED
df.loc[mask, "服开工单规范性质检"] = S_NOT_PASSED
# %%
# 弱光核查
df["弱光核查"] = S_BYPASSED

# %%
# 通话记录核查
df_tonghua = pd.read_excel(
    FP_TONGHUA,
    usecols=("宽带号码", "本月通话累计时长（分钟）"),
    dtype={
        "宽带号码": "string",
        "本月通话累计时长（分钟）": "float",
    },
)
tonghua_ok = df_tonghua.loc[
    df_tonghua["本月通话累计时长（分钟）"] > 0, "宽带号码"
]
df["通话记录核查"] = S_NOT_PASSED
df.loc[df["宽带帐号"].isin(tonghua_ok), "通话记录核查"] = S_PASSED


# %%
# 宽带稽核是否通过核查
df_kuandai = pd.read_pickle(FP_KUANDAI_JIHE_PKL)
df_tmp = df[["CRM业务流水号", "宽带帐号"]].merge(
    df_kuandai[["宽带帐号", "地市稽核结果"]],
    on="宽带帐号",
    how="left",
)
df["宽带稽核是否通过核查"] = S_PASSED
mask = df["CRM业务流水号"].isin(
    df_tmp[df_tmp["地市稽核结果"] == S_NOT_PASSED]["CRM业务流水号"]
)
df.loc[mask, "宽带稽核是否通过核查"] = S_NOT_PASSED

# %%
CHECK_COLUMNS = [
    "90天同址同帐号先拆后装核查",
    "当月同帐号先装后拆核查",
    "90天内同帐号先装后移核查",
    "90内同帐号多次移机核查",
    "当月相同联系电话装机工单核查",
    "当月新增一址多户工单核查",
    "登录端口异常工单核查",
    "当月登录端口与资源系统不一致核查",
    "服开工单规范性质检",
    "弱光核查",
    "通话记录核查",
    "宽带稽核是否通过核查",
]
not_passed = (df[CHECK_COLUMNS] == S_NOT_PASSED).any(axis=1)
df["地市稽核结果"] = S_PASSED
df.loc[not_passed, "地市稽核结果"] = S_NOT_PASSED

# %%
# IMS开通工单稽核结果
columns = [
    "工单号",
    "CRM单号",
    "宽带号码",
    "固话号码",
    "操作类型",
    "派单时间",
    "归档日期",
    "接入模式",
    "90天同址同帐号先拆后装核查",
    "当月同帐号先装后拆核查",
    "90天内同帐号先装后移核查",
    "90内同帐号多次移机核查",
    "当月相同联系电话装机工单核查",
    "当月新增一址多户工单核查",
    "登录端口异常工单核查",
    "当月登录端口与资源系统不一致核查",
    "服开工单规范性质检",
    "弱光核查",
    "通话记录核查",
    "系统稽核结果",
]
df_sgs = pd.read_excel(FP_JIHE_MINGXI, usecols=columns)
df_sgs = df_sgs.merge(
    df[["工单号", "宽带稽核是否通过核查", "地市稽核结果"]],
    on="工单号",
    how="left",
)

# %%
# 地市与系统稽核不一致原因
def explain(x):
    reasons = []
    for col in CHECK_COLUMNS:
        if (x[f"{col}_cz"] == S_NOT_PASSED) and (
            x[f"{col}_sgs"] == S_PASSED
        ):
            reasons.append(col)

    return ";".join(reasons)


df_tmp = df_sgs.merge(
    df,
    on="工单号",
    how="left",
    suffixes=("_sgs", "_cz"),
)
df_sgs["地市与系统稽核不一致原因"] = df_tmp.apply(explain, axis=1)

# %%
# IMS开通稽核地市铁通核对
df_sgs.loc[~df_sgs.工单号.isin(df.工单号), "市缺"] = S_YES
df_sgs.to_excel(
    f"report/4-IMS装移机/{year}{month:02}_IMS开通稽核地市铁通核对.xlsx",
    index=False,
)

# %%
# 完成：IMS装移机工单地市稽核明细表
df = df.merge(
    df_sgs[["工单号", "系统稽核结果"]], on="工单号", how="left"
)
df.loc[~df.工单号.isin(df_sgs.工单号), "省缺"] = S_YES
df.to_excel(
    f"report/4-IMS装移机/{year}{month:02}_IMS装移机工单地市稽核明细表.xlsx",
    index=False,
)
